package com.ncp.model.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;


public class DB {
	private PreparedStatement pstmt;
	public PreparedStatement getPstmt() {
		return pstmt;
	}
	private Connection conn;
	private String sql;
	private List<Object> list=new ArrayList<Object>();
	

	
	public String getProductItem(){
		
		String Item=null;
		try {
			conn = DbConnection.getConnByDataSource();
			pstmt=conn.prepareStatement(sql);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()){
				Item=rs.getString(1);
			}
			
			return Item;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return Item;
		}finally
		{
			closeSQL(conn, pstmt);
		}
    }
	
	
    public int getRowCount(){
		
		int rowCount=0;
		try {
			conn = DbConnection.getConnByDataSource();
			pstmt=conn.prepareStatement(sql);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()){
				rowCount=rs.getInt(1);
			}
			
			return rowCount;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return rowCount;
		}finally
		{
			closeSQL(conn, pstmt);
		}
    }
	//计算出一共有多少页
	public int getPageCount(int pageSize){
		
		int pageCount=0,rowCount=0;
		try {
			conn = DbConnection.getConnByDataSource();
			pstmt=conn.prepareStatement(sql);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()){
				rowCount=rs.getInt(1);

			}
			if(rowCount%pageSize==0){
				pageCount=rowCount/pageSize;
				
				
			}else{
				pageCount=rowCount/pageSize+1;

			}
			return pageCount;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return pageCount;
		}finally
		{
			closeSQL(conn, pstmt);
		}
   }
	
   public int setUpdate(){
		
	

		try {
			conn = DbConnection.getConnByDataSource();
			pstmt=conn.prepareStatement(sql);
			
			
			return pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}finally
		{
			closeSQL(conn, pstmt);
		}
    }
 //查询
	public Result executeQuery()
	{
		try {
			pstmt=conn.prepareStatement(sql);
			if (list.size()!=0) {
				for (int i = 0; i < list.size(); i++) {
					pstmt.setObject(i+1, list.get(i));
				}
			}
			ResultSet rs=pstmt.executeQuery();
			return ResultSupport.toResult(rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}finally
		{
			closeSQL(conn, pstmt);
		}
	}
	//增删改
	public int executeUpdate()
	{
		try {
			pstmt=conn.prepareStatement(sql);
			if (list.size()!=0) {
				for (int i = 0; i < list.size(); i++) {
					pstmt.setObject(i+1, list.get(i));
				}
			}
			return pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}finally
		{
			closeSQL(conn, pstmt);
		}
	}
		
	public void closeSQL(Connection conn,PreparedStatement pstmt)
	{
		if (pstmt!=null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public Connection getConn() {
		return conn;
	}
	public List<Object> getList() {
		return list;
	}
	public String getSql() {
		return sql;
	}
	public void setConn(Connection conn) {
		this.conn = conn;
	}
	public void setList(List<Object> list) {
		this.list = list;
	}
	public void setSql(String sql) {
		this.sql = sql;
	}
}
